Autonomous transactions are a bit like that dark part of the forest, beyond the rickety bridge, where the shadows are shaped like witches and bats swarm soundlessly from the trees. We all know it’s there, but the wise are in no hurry to visit.

Here, however, is a definition: an autonomous transaction is a completely independent transaction that is started by temporarily suspending the parent/calling transaction, which is then resumed after the independent transaction is completed. Or, if you prefer a real-world analogy: an autonomous transaction is a little like getting married, but having an affair on the side.

Morality isn’t the reason autonomous transactions are frowned upon; they possess the potential to get out of hand. Autonomous transactions are completely self-contained transactions that commit independently, and so to start one is to juggle two transactions (or more: you could, if you were extremely reckless, nest many levels of transactions within each other – a bit like that movie, Inception).

Permit me one last movie reference: do you remember how, in Back to the Future, Marty McFly travels back in time and meets a younger version of his mother? She is a little sweet on him; however, if she falls for him she would never fall for his father and he, Marty, would never be born. It’s a convoluted example, but autonomous transactions face a similar danger. Imagine that your original transaction updates a number of rows in a table and then, without committing, launches an autonomous transaction that goes and deletes those very rows. Chaos! Or, if you prefer technical terms, deadlock. Both transactions will obtain a lock on the table and attempt to stare each other down; and since neither one of them would back down, you will spend a long time wondering why your code has not finished executing – until you are put out of your misery by “ORA-00060: deadlock detected while waiting for resource.”

Many developers attempt to use autonomous transactions as a get-out-of-jail card, especially when they come across triggers that spit out that dreaded mutating table error (ORA-04091: table is mutating, trigger/function may not see it). Rather than reconsider the code or, perhaps, the design of the database, the temptation to wallpaper over the cracks by using an autonomous transaction can be great. But that can be a mistake. The mutating table error means that a trigger fired by a transaction on a table is attempting to edit that selfsame unstable table – is that the kind of warning you want to ignore?

Let us pause here to recap the story so far: autonomous transactions can get out of hand as they involve juggling multiple transactions simultaneously; they can lead to deadlocks; they can hide the effects of bad code and bad database design. One would be forgiven for wondering why Oracle ever created this poltergeist. The truth is that autonomous transactions are a bit like nunchucks – a recipe for unintended self-harm in most hands, but an instrument of pure beauty in the hands of Bruce Lee. If you know what you are doing, there is no reason why you cannot use an autonomous transaction to neatly resolve an issue.

One situation in which autonomous transactions can prove extremely useful is with auditing and debugging PL/SQL. Here is a situation that I am currently facing at work: I have a long body of PL/SQL code that carries out a number of complicated acts of data manipulation. Unfortunately, this procedure fails for one of our users. As you would expect it drops from whichever point that the error occurred down to the exception handling where it output an error message (the ever-unhelpful ORA-01403: No data found) and then it exited.

When it comes to figuring out what has gone wrong, the error message is, of course, a valuable clue. However, wouldn’t it be nice if I could get my hands on more information – perhaps details of how much of the procedure had executed before it failed, details of the state of my variables at the various points, perhaps even information about the PC the procedure is being run from? There’s no way you can get all of that from a mere error message.

And this is where an autonomous transaction can strap on its cape and swoop in to save the day.

First let’s create a table where we will store our debug information:

Now we can create a procedure that writes to the table. Note the keyword PRAGMA AUTONOMOUS_TRANSACTION, this is what makes our procedure an autonomous transaction. This particular case requires that we use a procedure; however, you can use a function – either standalone or within a package – and even anonymous blocks. And as I implied earlier, we can use triggers.

There are two things that are noteworthy in the above procedure. First is PRAGMA AUTONOMOUS_TRANSACTION; as I have said, this is what tells Oracle that we wish to launch an autonomous transaction. And second, the fact that we can commit our procedure without fear of affecting the state of the calling transaction. If, right after we have called our procedure from within another transaction we rolled back we would find that while all DML in that parent transaction would be undone, the data inserted within save_debug would stay committed.

Secure in this knowledge, I can sow calls to save_debug within my buggy procedure, using it to harvest information about the various states of my variables, any error messages raised, or any other information that I think might be of assistance. None of which would be easily achievable without autonomous transactions.

There are a couple of further points I need to make clear: you must end your autonomous transaction either by committing or rolling back all changes, otherwise Oracle will error; also, you cannot apply the autonomous transaction pragma to whole packages – instead you must apply it to individual procedures or functions within the package.

And that’s that, autonomous transactions. No longer that spooky, shadowed sliver of woodland where unseen things rustle in the leaves and we fear to tread.